import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.http.HtmlUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;

public class ReadFanyi {
    public static void main(String[] args) throws IOException {
//        huiChangMing();
//        huiChangWeizhi();
//        forumThemeDraft();
//        topicTitle();
//        guestWorkUnit();
        BigDecimal a = new BigDecimal("11");
        a.doubleValue();
        System.out.println(NumberUtil.formatPercent(0.33335, 2));
        System.out.println(HtmlUtil.filter("1"));
    }

    /**
     * 嘉宾工作单位
     */
    private static void guestWorkUnit() throws IOException {

        String sql = "update active_guest_work_unit set work_unit_en = '%s' where concat(work_unit, position) = '%s' and length(ifnull(work_unit_en, '')) = 0;\n";
        ExcelReader excelReader = ExcelUtil.getReader(new File("d://fanyi/译文-2021建设大会论坛列表 321072910.xlsx"));
        Sheet sheet = excelReader.getSheet();
        int rowSize = sheet.getLastRowNum();
        Map<String, String> data = new LinkedHashMap<>();
        for (int i = 1; i < rowSize; i++) {
            Row row = sheet.getRow(i);
            String keyStr = row.getCell(8).getStringCellValue().replace(" ", "").replace("，", ",");
            String[] key = keyStr.split("%");
            String valueStr = row.getCell(9).getStringCellValue().trim().replace("，", ",");
            String[] value = valueStr.split("%");
            for (int j = 0; j < key.length; j++) {
                if (!key[j].trim().contains(",")) {
//                    data.put(key[j].trim(),value[j].trim());
                } else if (keyStr.split(",").length == valueStr.split(",").length) {
                    for (int m = 0; m < key[j].split(",").length; m++) {
                        data.put(key[j].split(",")[m].trim(),value[j].split(",")[m].trim());
                    }
                }
            }

            keyStr = row.getCell(12).getStringCellValue().replace(" ", "").replace("，", ",");
            key = keyStr.split("%");
            valueStr = row.getCell(13).getStringCellValue().trim().replace("，", ",");
            value = valueStr.split("%");
            for (int j = 0; j < key.length; j++) {
                if (!key[j].trim().contains(",")) {
//                    data.put(key[j].trim(),value[j].trim());
                } else if (keyStr.split(",").length == valueStr.split(",").length) {
                    for (int m = 0; m < key[j].split(",").length; m++) {
                        data.put(key[j].split(",")[m].trim(),value[j].split(",")[m].trim());
                    }
                }
            }
        }

//        excelReader = ExcelUtil.getReader(new File("d://fanyi/译文-2021建设大会议题嘉宾列表.xlsx"));
//        sheet = excelReader.getSheet();
//        rowSize = sheet.getLastRowNum();
//        for (int i = 1; i < rowSize; i++) {
//            Row row = sheet.getRow(i);
//            String keyStr = row.getCell(8).getStringCellValue().replace(" ", "").replace("，", ",");
//            String[] key = keyStr.split("%");
//            String valueStr = row.getCell(9).getStringCellValue().trim().replace("，", ",");
//            String[] value = valueStr.split("%");
//            for (int j = 0; j < key.length; j++) {
//                if (!key[j].trim().contains(",")) {
//                    data.put(key[j].trim(),value[j].trim());
//                } else if (keyStr.split(",").length == valueStr.split(",").length) {
//                    for (int m = 0; m < key[j].split(",").length; m++) {
//                        data.put(key[j].split(",")[m].trim(),value[j].split(",")[m].trim());
//                    }
//                }
//            }
//        }

        StringBuilder stringBuilder = new StringBuilder();
        for (Map.Entry<String, String> d : data.entrySet()) {
            stringBuilder.append(String.format(sql, d.getValue(), StrUtil.replace(d.getKey(), "\r\n", "")).replaceAll("\n", ""));
        }
        System.out.println(stringBuilder);
    }

    /**
     * 嘉宾名称
     */
    private static void guestName() throws IOException {

        String sql = "update active_guest set name_en = '%s' where replace(name_cn, ' ', '') = '%s' and length(ifnull(name_en, '')) = 0;\n";
        ExcelReader excelReader = ExcelUtil.getReader(new File("d://fanyi/译文-2021建设大会论坛列表 321072910.xlsx"));
        Sheet sheet = excelReader.getSheet();
        int rowSize = sheet.getLastRowNum();
        Map<String, String> data = new LinkedHashMap<>();
        for (int i = 1; i < rowSize; i++) {
            Row row = sheet.getRow(i);
            String[] key = row.getCell(6).getStringCellValue().replace(" ", "").split("%");
            String[] value = row.getCell(7).getStringCellValue().trim().split("%");
            for (int j = 0; j < key.length; j++) {
                data.put(key[j].trim(),value[j].trim());
            }
            key = row.getCell(10).getStringCellValue().replace(" ", "").split("%");
            value = row.getCell(11).getStringCellValue().trim().split("%");
            for (int j = 0; j < key.length; j++) {
                data.put(key[j].trim(), value[j].trim());
            }
        }

        excelReader = ExcelUtil.getReader(new File("d://fanyi/译文-2021建设大会议题嘉宾列表.xlsx"));
        sheet = excelReader.getSheet();
        rowSize = sheet.getLastRowNum();
        for (int i = 1; i < rowSize; i++) {
            Row row = sheet.getRow(i);
            String[] key = row.getCell(6).getStringCellValue().replace(" ", "").split("%");
            String[] value = row.getCell(7).getStringCellValue().trim().split("%");
            for (int j = 0; j < key.length; j++) {
                data.put(key[j].trim(),value[j].trim());
            }
        }

        StringBuilder stringBuilder = new StringBuilder();
        for (Map.Entry<String, String> d : data.entrySet()) {
            stringBuilder.append(String.format(sql, d.getValue(), StrUtil.replace(d.getKey(), "\r\n", "")).replaceAll("\n", ""));
        }
        System.out.println(stringBuilder);
    }

    /**
     * 论坛议题（草稿）
     */
    private static void topicTitle() throws IOException {
        ExcelReader excelReader = ExcelUtil.getReader(new File("d://fanyi/译文-2021建设大会议题嘉宾列表.xlsx"));
        Sheet sheet = excelReader.getSheet();
        int rowSize = sheet.getLastRowNum();
        String sql = "update active_forum_topic set topic_title_en = '%s' where replace(topic_title_cn, ' ', '') = '%s';\n";
        Map<String, String> data = new LinkedHashMap<>();
        for (int i = 1; i < rowSize; i++) {
            Row row = sheet.getRow(i);
            data.put(row.getCell(0).getStringCellValue().replace(" ", ""), row.getCell(1).getStringCellValue().trim());
        }

        StringBuilder stringBuilder = new StringBuilder();
        for (Map.Entry<String, String> d : data.entrySet()) {
            stringBuilder.append(String.format(sql, d.getValue(), StrUtil.replace(d.getKey(), "\r\n", "")).replaceAll("\n", ""));
        }
        System.out.println(stringBuilder);
    }

    /**
     * 论坛议题（草稿）
     */
    private static void topicTitleDraft() throws IOException {
        ExcelReader excelReader = ExcelUtil.getReader(new File("d://fanyi/译文-2021建设大会议题嘉宾列表.xlsx"));
        Sheet sheet = excelReader.getSheet();
        int rowSize = sheet.getLastRowNum();
        String sql = "update active_forum_topic_draft set topic_title_en = '%s' where replace(topic_title_cn, ' ', '') = '%s';\n";
        Map<String, String> data = new LinkedHashMap<>();
        for (int i = 1; i < rowSize; i++) {
            Row row = sheet.getRow(i);
            data.put(row.getCell(0).getStringCellValue().replace(" ", ""), row.getCell(1).getStringCellValue().trim().replace("'","\\'"));
        }

        StringBuilder stringBuilder = new StringBuilder();
        for (Map.Entry<String, String> d : data.entrySet()) {
            stringBuilder.append(String.format(sql, d.getValue(), StrUtil.replace(d.getKey(), "\r\n", "")).replaceAll("\n", ""));
        }
        System.out.println(stringBuilder);
    }

    /**
     * 论坛主题（草稿）
     */
    private static void forumThemeDraft() throws IOException {
        ExcelReader excelReader = ExcelUtil.getReader(new File("d://fanyi/译文-2021建设大会论坛列表 321072910.xlsx"));
        Sheet sheet = excelReader.getSheet();
        int rowSize = sheet.getLastRowNum();
        String sql = "update active_forum_draft set forum_theme_en = '%s' where replace(forum_theme_cn, ' ', '') = '%s';\n";
        Map<String, String> data = new LinkedHashMap<>();
        for (int i = 1; i < rowSize; i++) {
            Row row = sheet.getRow(i);
            data.put(row.getCell(0).getStringCellValue().replace(" ", ""), row.getCell(1).getStringCellValue().trim());
        }

        StringBuilder stringBuilder = new StringBuilder();
        for (Map.Entry<String, String> d : data.entrySet()) {
            stringBuilder.append(String.format(sql, d.getValue(), StrUtil.replace(d.getKey(), "\r\n", "")).replaceAll("\n", ""));
        }
        System.out.println(stringBuilder);
    }

    /**
     * 论坛主题
     */
    private static void forumTheme() throws IOException {
        ExcelReader excelReader = ExcelUtil.getReader(new File("d://fanyi/译文-2021建设大会论坛列表 321072910.xlsx"));
        Sheet sheet = excelReader.getSheet();
        int rowSize = sheet.getLastRowNum();
        String sql = "update active_forum set forum_theme_en = '%s' where replace(forum_theme_cn, ' ', '') = '%s';\n";
        Map<String, String> data = new LinkedHashMap<>();
        for (int i = 1; i < rowSize; i++) {
            Row row = sheet.getRow(i);
            data.put(row.getCell(0).getStringCellValue().replace(" ", ""), row.getCell(1).getStringCellValue().trim());
        }

        StringBuilder stringBuilder = new StringBuilder();
        for (Map.Entry<String, String> d : data.entrySet()) {
            stringBuilder.append(String.format(sql, d.getValue(), StrUtil.replace(d.getKey(), "\r\n", "")).replaceAll("\n", ""));
        }
        System.out.println(stringBuilder);
    }

    /**
     * 翻译会场位置
     */
    private static void huiChangWeizhi() {
        ExcelReader excelReader = ExcelUtil.getReader(new File("d://fanyi/译文-2021建设大会论坛列表 321072910.xlsx"));
        Sheet sheet = excelReader.getSheet();
        int rowSize = sheet.getLastRowNum();
        String sql = "update active_forum_venue set venue_location_en = '%s' where replace(venue_location_cn, ' ', '') = '%s';";
        Map<String, String> data = new HashMap<>();
        for (int i = 1; i < rowSize; i++) {
            Row row = sheet.getRow(i);
            data.put(row.getCell(16).getStringCellValue().replace(" ", ""), row.getCell(17).getStringCellValue());
        }
        for (Map.Entry<String, String> d : data.entrySet()) {
            System.out.println(String.format(sql, d.getValue(), d.getKey()));
        }
    }

    /**
     * 翻译会场名
     */
    private static void huiChangMing() {
        ExcelReader excelReader = ExcelUtil.getReader(new File("d://fanyi/译文-2021建设大会论坛列表 321072910.xlsx"));
        Sheet sheet = excelReader.getSheet();
        int rowSize = sheet.getLastRowNum();
        String sql = "update active_forum_venue set venue_name_en = '%s' where replace(venue_name_cn, ' ', '') = '%s';";
        Map<String, String> data = new HashMap<>();
        for (int i = 1; i < rowSize; i++) {
            Row row = sheet.getRow(i);
            data.put(row.getCell(14).getStringCellValue().replace(" ", ""), row.getCell(15).getStringCellValue());
        }
        for (Map.Entry<String, String> d : data.entrySet()) {
            System.out.println(String.format(sql, d.getValue(), d.getKey()));
        }
    }
}
